from jira import JIRA
import smtplib
from email.mime.text import MIMEText
from datetime import datetime, timedelta
import pandas as pd
from io import StringIO

# 初始化Jira客户端
jira_url = 'http://*********com/'
username = '******'
password = '********'

# 连接到Jira服务器
jira = JIRA(server=jira_url, basic_auth=(username, password))

# 指定过滤器的ID
filter_id = '27200'  # 示例ID，请替换为实际的过滤器ID

# 获取过滤器的详情
filter = jira.filter(filter_id)

# 获取过滤器的查询结果（issues）
issues = jira.search_issues(filter.jql, maxResults=1000)  # 可调整maxResults来获取更多问题

# 定义时间格式
def convert_time_format(time_str):
    """
    将Jira的时间格式转换为 'x月x日' 格式。
    """
    # 假设时间字符串格式是 '%Y-%m-%dT%H:%M:%S.%f%z'
    dt = datetime.strptime(time_str, '%Y-%m-%dT%H:%M:%S.%f%z')
    return dt.strftime('%m月%d日')

# 转换工单耗时函数，单位为秒，转换为小时并保留2位小数
def convert_timespent_to_hours(timespent):
    try:
        # 将秒数转换为小时，并保留两位小数
        if timespent != 'N/A':
            hours = timespent / 3600
            return f"{hours:.2f}"
        else:
            return 'N/A'
    except Exception as e:
        return 'N/A'

# 统计总工单数和每种工单等级的数量
total_issues = len(issues)
priority_counts = {}
for issue in issues:
    priority = issue.fields.priority.name if getattr(issue.fields, 'priority', None) else 'N/A'
    priority_counts[priority] = priority_counts.get(priority, 0) + 1

# 列标题和对应的宽度及对齐方式
columns = {
    '工单主题': {'width': 'auto', 'line-height': '38px','alignment': 'left'  },
    '工单单号': {'width': '88px', 'line-height': '38px','alignment': 'center'},
    '工单等级': {'width': '88px', 'line-height': '38px','alignment': 'center'},
    '工单耗时': {'width': '88px', 'line-height': '38px','alignment': 'center'},
    '任务类型': {'width': '88px', 'line-height': '38px', 'alignment': 'center'},
    '创建时间': {'width': '136px','line-height': '38px','alignment': 'center'},
    '完成时间': {'width': '136px','line-height': '38px','alignment': 'center'},
    '工单状态': {'width': '88px', 'line-height': '38px', 'alignment': 'center'},
    '运维人员': {'width': '88px', 'line-height': '38px', 'alignment': 'center'}
}


def convert_time_format(time_str):
    """
    将 '2025-02-28T20:44:38.000+0800' 格式的字符串转换为 'x月x日'.
    """
    # 解析原始时间字符串
    dt = datetime.strptime(time_str, '%Y-%m-%dT%H:%M:%S.%f%z')
    # 转换为目标格式
    return dt.strftime('%m月%d日')


# 转换为 HTML 表格
def convert_to_html_table(issues):

    # 开始表格的 HTML
    html_table = "<table border='1' style='border-collapse: collapse; width: 1366px;'>\n"

    # 添加运维日报标题行
    html_table += "<thead>\n"
    html_table += "<tr><th colspan='11' style='text-align: center; font-size: 20px; font-weight: bold; padding: 10px; line-height: 38px; background-color: #f2f2f2;'>运维周报</th></tr>\n"
    html_table += "<tr>"

    # 根据 columns 字典动态生成表头
    html_table += "<tr>"
    html_table += "<th style='text-align: left; width : auto'>项目名称</th>"  # 为组件添加一列
    for column_name, settings in columns.items():
        width = settings['width']
        alignment = settings['alignment']
        html_table += f"<th style='width: {width}; text-align: {alignment}; height: 28px;'>{column_name}</th>"
    html_table += "</tr>\n"
    html_table += "</thead>\n"

    html_table += "<tbody>\n"

    for issue in issues:
        # 获取每个工单的相关字段
        summary = issue.fields.summary  # 工单主题
        key = issue.key                 # 工单单号
        priority = issue.fields.priority.name if issue.fields.priority else 'N/A'     # 工单等级
        timespent = convert_timespent_to_hours(issue.fields.timespent)  # 将工单耗时转换为小时
        customfield_13001 = issue.fields.customfield_13001 if issue.fields.priority else 'N/A' # 任务类型
        created = convert_time_format(issue.fields.created)  # 转换 Created 时间
        updated = convert_time_format(issue.fields.updated)  # 转换 Updated 时间
        status = issue.fields.status.name if issue.fields.status else 'N/A'           # 工单状态
        assignee = issue.fields.assignee.displayName if issue.fields.assignee else 'Unassigned'  # 负责人

        # 获取组件信息
        components = issue.fields.components
        components_list = ", ".join([component.name for component in components]) if components else 'N/A'

        # 生成每一行的数据，按照列顺序排列
        html_table += "<tr  style='height: 28px;'>"
        html_table += f"<td style='text-align: left; '>{components_list}</td>"   # 项目名称
        html_table += f"<td style='text-align: {columns['工单主题']['alignment']};'>{summary}</td>"
        html_table += f"<td style='text-align: {columns['工单单号']['alignment']};'>{key}</td>"
        html_table += f"<td style='text-align: {columns['工单等级']['alignment']};'>{priority}</td>"
        html_table += f"<td style='text-align: {columns['工单耗时']['alignment']};'>{timespent}</td>"
        html_table += f"<td style='text-align: {columns['任务类型']['alignment']};'>{customfield_13001}</td>"
        html_table += f"<td style='text-align: {columns['创建时间']['alignment']};'>{created}</td>"
        html_table += f"<td style='text-align: {columns['完成时间']['alignment']};'>{updated}</td>"
        html_table += f"<td style='text-align: {columns['工单状态']['alignment']};'>{status}</td>"
        html_table += f"<td style='text-align: {columns['运维人员']['alignment']};'>{assignee}</td>"
        html_table += "</tr>\n"



    html_table += "</tbody>\n"
    html_table += "</table>\n"


    return html_table


# 将JIRA问题数据转换为HTML表格
html_table = convert_to_html_table(issues)

def convert_to_html_table2(issues):

    # 开始表格的 HTML
    html_table2 = "<table border='1' style='border-collapse: collapse; width: 1366px;'>\n"

    # 添加运维日报标题行
    html_table2 += "<thead>\n"
    html_table2 += "<tr><th colspan='11' style='text-align: center; font-size: 20px; font-weight: bold; padding: 10px; line-height: 38px; background-color: #f2f2f2;'>运维周报统计</th></tr>\n"
    html_table2 += "<tr>"
    html_table2 += "<tbody><tr><td colspan='10' style='line-height: 40px; height: 40px; font-size: 20px;'><strong>统计信息:<br></strong> 本周系统运维总计 {} 例工单：<br>".format(
        total_issues)
    for priority, count in priority_counts.items():
        html_table2 += " 等级 <strong>{}</strong> 共有 {} 个；<br>".format(priority, count)
    html_table2 = html_table2.rstrip(',') + '</td></tr>'
    html_table2 += "</tbody>\n"
    html_table2 += "</table>\n"

    return html_table2

# 将JIRA问题数据转换为HTML表格
html_table2 = convert_to_html_table2(issues)

# print(html_table)
# def convert_html_to_dataframe(html_content):
#     # 使用 StringIO 包裹 HTML 字符串以供 read_html 使用
#     df = pd.read_html(StringIO(html_content))[0]
#     return df
#
# # 处理 html_table 到 DataFrame
# df1 = convert_html_to_dataframe(html_table)
#
# # 对于 html_table2，因为它的结构不同于标准表格，我们手动创建 DataFrame
# data = {
#     '统计信息': [f"本周系统运维总计 {total_issues} 例工单："]
# }
# for priority, count in priority_counts.items():
#     data['统计信息'].append(f"等级 {priority} 共有 {count} 个；")
# df2 = pd.DataFrame(data)
#
# # 设置 Excel 文件路径
# current_year, current_week, _ = datetime.now().isocalendar()
# excel_file_path = f"运维报告_{current_year}_周{current_week}.xlsx"
#
# # 使用 ExcelWriter 将多个 DataFrame 写入不同的工作表
# with pd.ExcelWriter(excel_file_path, engine='openpyxl') as writer:
#     df1.to_excel(writer, sheet_name='运维周报', index=False)
#     df2.to_excel(writer, sheet_name='运维周报统计', index=False)
#
# print(f"Excel 文件已生成: {excel_file_path}")

#电子邮件设置
sender_email = "kechen@*******.com"
receiver_email = "kechen@*******.com"
#cc_emails = ["zhenjiehuang@****.com","fulongzhou@*****.com"]

# 获取当前日期
current_date = datetime.now().strftime("%Y-%m-%d")

# 获取当前日期的ISO日历年和周数
current_year, current_week, _ = datetime.now().isocalendar()

# 当前日期
today = datetime.now()

# 计算上一周的同一天
last_week_day = today - timedelta(weeks=1)

# 获取上一周的 ISO 年、周数
last_year, last_week, _ = last_week_day.isocalendar()

# 设置邮件标题为包含当前年的第几周
subject = f"{current_year}年度第{last_week}周运维报告"

# 输出结果
print(subject)

body = f"Dear Boss,<br><br>Please find the JIRA issues below:<br><br>{html_table2}<br><br><br>{html_table}<br>Best Regards,<br>Your JIRA Bot"

# 设置邮件内容
msg = MIMEText(body, 'html')
msg['Subject'] = subject
msg['From'] = sender_email
#msg['To'] = receiver_email

# 设置抄送字段
#msg['Cc'] = ", ".join(cc_emails)

# 发送邮件
try:
    with smtplib.SMTP_SSL('smtp.exmail.qq.com', 465) as server:
        server.login(sender_email, '******')  # 登录SMTP服务器
        server.sendmail(sender_email, receiver_email, msg.as_string())  # 发送邮件
        print("邮件已发送")
except Exception as e:
    print(f"邮件发送失败: {e}")

